package orest.back.userneeds;

import java.sql.Connection;
import java.sql.Date;

import orest.back.market.MarketHandler;

import net.ssta.core.base.SQLBuilderBase;
import net.ssta.core.web.RequestKit;



/**
 * 
 * @ClassName:
 * @Description: 
 * @author: 
 * @date: 
 * 
 */
public class UserNeedsSql extends SQLBuilderBase
{
    /*
     * m_builder : 单例实例
     */
    private static UserNeedsSql m_builder = new UserNeedsSql();
    
    /**
     * @Title: getSQLBuilder
     * @Description: 获取实例
     * 
     * @param conn 数据库连
     * @return 单例实例
     */
    public static UserNeedsSql getSQLBuilder(Connection conn)
    {
        return m_builder;
    }
    
    /**
     * 用户项目需求列表sql语句
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String getKjcgNeedsList(RequestKit kit)
    {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT T.* FROM T_NEEDFORACHIEVE T WHERE 1=1 ");
        
        //标题
        String keyword = kit.getParameter("keyword","");
        if(!keyword.equals("")){
        	sb.append(" AND T.TITLE LIKE '%").append(kit.getParameter("keyword","")).append("%'");
        }
        
        //提交人
        String username = kit.getParameter("USERNAME", "");
        if(!"".equals(username)){
        	sb.append(" AND LINKMAN LIKE '%").append(username).append("%' ");
        }
        
        //机构名称
        String unitname = kit.getParameter("UNITNAME", "");
        if(!"".equals(unitname)){
        	sb.append(" AND UNITNAME LIKE '%").append(unitname).append("%' ");
        }
        
        //提交时间
        Date today = new Date(System.currentTimeMillis());
        String datetype = kit.getParameter("SUBMITDATE", "");
        if("1".equals(datetype)){
        	sb.append(" AND T.SUBMITDATE='").append(today).append("' ");
        }else if("2".equals(datetype)){
        	String weekday = MarketHandler.getCalculateDate(0, 0, -7);
        	sb.append(" AND T.SUBMITDATE>='").append(weekday).append("' ");
        }else if("3".equals(datetype)){
        	String monthday = MarketHandler.getCalculateDate(0, -1, 0);
        	sb.append(" AND T.SUBMITDATE>='").append(monthday).append("' ");
        }else if("4".equals(datetype)){
        	String halfyearday = MarketHandler.getCalculateDate(0, -6, 0);
        	sb.append(" AND T.SUBMITDATE>='").append(halfyearday).append("' ");
        }
        
        //处理状态
        String ifprocess = kit.getParameter("IFPROCESS", "");
        if(!"".equals(ifprocess)){
        	sb.append(" AND T.IFPROCESS = '").append(ifprocess).append("' ");
        }
        
        sb.append(" ORDER BY SUBMITDATE DESC");
        return sb.toString();
    }
    
    
    /**
     * 更改项目需求状态
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String updateKjcgStatus(String status,String kjcgUuids)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" UPDATE T_NeedForAchieve  SET IFPROCESS='").append(status).append("'");
        String kjcgUuidArr[] = kjcgUuids.split(",");
        if(kjcgUuids!=null&&!kjcgUuids.equals("")){
        	sb.append(" WHERE ID IN (");
	        for(int i=0;i<kjcgUuidArr.length;i++){
	        	sb.append("'").append(kjcgUuidArr[i]).append("'");
	        	if(i<kjcgUuidArr.length-1){
	        		sb.append(",");
	        	}
	        }
	        sb.append(")");
        }
        return sb.toString();
    }
    
    
    
    /**
     * 删除项目需求状态
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String deleteKjcgUserNeeds(String kjcgUuids)
    {
        StringBuffer sb = new StringBuffer();
        sb.append("DELETE FROM T_NEEDFORACHIEVE ");
        String kjcgUuidArr[] = kjcgUuids.split(",");
    	sb.append(" WHERE ID IN (");
        for(int i=0;i<kjcgUuidArr.length;i++){
        	sb.append("'").append(kjcgUuidArr[i]).append("'");
        	if(i<kjcgUuidArr.length-1){
        		sb.append(",");
        	}
        } 
	    sb.append(")");
        return sb.toString();
    }
    
    
    
    /**
     * 删除项目需求状态
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String deleteKjcgNeedProcess(String kjcgUuids)
    {
        StringBuffer sb = new StringBuffer();
        sb.append("DELETE FROM T_KJCGNEEDPROCESS ");
        String kjcgUuidArr[] = kjcgUuids.split(",");
    	sb.append(" WHERE NEEDID IN (");
        for(int i=0;i<kjcgUuidArr.length;i++){
        	sb.append("'").append(kjcgUuidArr[i]).append("'");
        	if(i<kjcgUuidArr.length-1){
        		sb.append(",");
        	}
        } 
	    sb.append(")");
        return sb.toString();
    }
    
    
    
    
    /**
     * 用户专家需求列表sql语句
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String getExpertNeedsList(RequestKit kit)
    {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT * FROM T_NEEDFOREXPERT T WHERE 1=1 ");
        
        //从事领域
        String expfield = kit.getParameter("SEA_EXPNEEDEDFIELD","");
        
        if(expfield!=null&&!expfield.equals("")){
        	sb.append(" AND T.EXPNEEDEDFIELD = '").append(expfield).append("' ");
        }
        
        //提交人
        String username = kit.getParameter("USERNAME", "");
        if(!"".equals(username)){
        	sb.append(" AND LINKMAN LIKE '%").append(username).append("%' ");
        }
        
        //机构名称
        String unitname = kit.getParameter("UNITNAME", "");
        if(!"".equals(unitname)){
        	sb.append(" AND UNITNAME LIKE '%").append(unitname).append("%' ");
        }
        
        //提交时间
        Date today = new Date(System.currentTimeMillis());
        String datetype = kit.getParameter("SUBMITDATE", "");
        if("1".equals(datetype)){
        	sb.append(" AND T.SUBMITDATE='").append(today).append("' ");
        }else if("2".equals(datetype)){
        	String weekday = MarketHandler.getCalculateDate(0, 0, -7);
        	sb.append(" AND T.SUBMITDATE>='").append(weekday).append("' ");
        }else if("3".equals(datetype)){
        	String monthday = MarketHandler.getCalculateDate(0, -1, 0);
        	sb.append(" AND T.SUBMITDATE>='").append(monthday).append("' ");
        }else if("4".equals(datetype)){
        	String halfyearday = MarketHandler.getCalculateDate(0, -6, 0);
        	sb.append(" AND T.SUBMITDATE>='").append(halfyearday).append("' ");
        }
        
        //处理状态
        String ifprocess = kit.getParameter("IFPROCESS", "");
        if(!"".equals(ifprocess)){
        	sb.append(" AND T.IFPROCESS = '").append(ifprocess).append("' ");
        }
        
        
        sb.append(" ORDER BY T.SUBMITDATE DESC");
        return sb.toString();
    }
    
    
    
    
    /**
     * 更改专家需求状态
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String updateExpertStatus(String status,String expertUuids)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" UPDATE T_NEEDFOREXPERT SET IFPROCESS='").append(status).append("'");
        String expertUuidArr[] = expertUuids.split(",");
        if(expertUuids!=null&&!expertUuids.equals("")){
        	sb.append(" WHERE ID IN (");
	        for(int i=0;i<expertUuidArr.length;i++){
	        	sb.append("'").append(expertUuidArr[i]).append("'");
	        	if(i<expertUuidArr.length-1){
	        		sb.append(",");
	        	}
	        }
	        sb.append(")");
        }
        return sb.toString();
    }
    
    
    
    /**
     * 删除用户专家需求
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String deleteExpertNeeds(String expertUuids)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" DELETE FROM  T_NEEDFOREXPERT ");
        String expertUuidArr[] = expertUuids.split(",");
    	sb.append(" WHERE ID IN (");
        for(int i=0;i<expertUuidArr.length;i++){
        	sb.append("'").append(expertUuidArr[i]).append("'");
        	if(i<expertUuidArr.length-1){
        		sb.append(",");
        	}
        }
        sb.append(")");
        return sb.toString();
    }
    
    
    
    /**
     * 删除用户专家需求处理情况
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String deleteExpertNeedsProcess(String expertUuids)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" DELETE FROM T_EXPERTNEEDPROCESS ");
        String expertUuidArr[] = expertUuids.split(",");
    	sb.append(" WHERE NEEDID IN (");
        for(int i=0;i<expertUuidArr.length;i++){
        	sb.append("'").append(expertUuidArr[i]).append("'");
        	if(i<expertUuidArr.length-1){
        		sb.append(",");
        	}
        }
        sb.append(")");
        return sb.toString();
    }
    
    
    
    /**
     * 用户项目需求列表sql语句
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String getFildOption(String tablename,String fieldname )
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT T.TABLENAME,T.FIELDNAME,T.OPTIONVALUE,T.OPTIONNAME FROM T_FIELDOPTION T ");
        sb.append(" WHERE T.TABLENAME='").append(tablename).append("'");
        sb.append(" AND T.FIELDNAME='").append(fieldname).append("'");
        sb.append(" ORDER BY T.TABLENAME,T.FIELDNAME,T.OPTIONVALUE,T.OPTIONNAME");
        return sb.toString();
    }
    
    /**
     * 根据code获取zdxx
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String getZdxx(String code)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT T.XXDM,T.XXMC FROM ZDXX T WHERE T.ZDDM='").append(code);
        sb.append("'");
        sb.append(" ORDER BY T.SORT");
        return sb.toString();
    }
    
    
    /**
     * 从province表获取所有省
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String getAllProvince()
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT * FROM PROVINCE ORDER BY PROVINCE_ID");
        return sb.toString();
    }
    
    
    /**
     * 根据需求Id获取项目需求详细信息
     * **/
    public static String getKjcgNeedsByIdSql(String uuid){
    	StringBuffer sb = new StringBuffer();
    	sb.append(" SELECT T.*,P.CONTENT,P.SUBMITDATE AS PSUBMITDATE,U.FULLNAME FROM T_NEEDFORACHIEVE T LEFT JOIN T_KJCGNEEDPROCESS P ON T.ID=P.NEEDID LEFT JOIN V_REGISTER_USER U ON P.USERID=U.USERID WHERE T.ID ='").append(uuid).append("' ");
    	return sb.toString();
    }
    
    /**
     * 根据需求Id获取专家需求详细信息
     * **/
    public static String getExpertNeedsByIdSql(String uuid){
    	StringBuffer sb = new StringBuffer();
    	sb.append(" SELECT T.*,P.CONTENT,P.SUBMITDATE AS PSUBMITDATE,U.FULLNAME FROM T_NEEDFOREXPERT T LEFT JOIN T_EXPERTNEEDPROCESS P ON T.ID=P.NEEDID LEFT JOIN V_REGISTER_USER U ON P.USERID=U.USERID WHERE T.ID ='").append(uuid).append("' ");
    	//sb.append(" SELECT T.*,P.CONTENT FROM T_NEEDFOREXPERT T LEFT JOIN T.EXPERTNEEDPROCESS P ON T.ID=P.NEEDID WHERE T.ID ='").append(uuid).append("' ");
    	return sb.toString();
    }
    
    /**
     * 根据需求Id获取项目需求处理情况信息
     * **/
    public static String getKjcgNeedsHandleByIdSql(String uuid){
    	StringBuffer sb = new StringBuffer();
    	sb.append(" SELECT * FROM T_KJCGNEEDPROCESS WHERE NEEDID ='").append(uuid).append("' ");
    	return sb.toString();
    }
    /**
     * 根据需求Id获取专家需求处理情况信息
     * **/
    public static String getExpertNeedsHandleByIdSql(String uuid){
    	StringBuffer sb = new StringBuffer();
    	sb.append(" SELECT * FROM T_EXPERTNEEDPROCESS WHERE NEEDID ='").append(uuid).append("' ");
    	return sb.toString();
    }
}
